import pandas as pd
import re

def extract_and_standardize_date(s):
    """去除括号和空格后，提取并返回日期字符串的后四位"""
    # 去除括号和所有空格
    cleaned_str = re.sub(r'[()\s]', '', s)
    
    # 查找六位或八位的数字序列
    match = re.search(r'\d{8}|\d{6}|\d{4}', cleaned_str)
    if match:
        date_str = match.group()
        # 返回日期的后四位
        date = date_str[-4:]
        if date == "2405":
            print(date_str)
            print(cleaned_str)
        return date
    else:
        print(f"{s}找不到日期")
        return None  # 如果找不到数字序列，则返回None

def get_name_money_list(excel_file, useName):
    # 使用pandas读取Excel文件
    xls = pd.ExcelFile(excel_file)
    name_list = []
    money_list = []
    # 遍历每一个sheet
    for sheet_name in xls.sheet_names:
        if sheet_name == '处方目录单价' or sheet_name.count('模版')>0 or sheet_name == '处方结算清单':
            continue
        # 读取当前sheet
        try:
            df = xls.parse(sheet_name, encoding='utf8')  # 假设Excel文件的编码为gbk，根据实际情况调整
        except Exception as e:
            print(f"读取sheet '{sheet_name}' 时发生错误: {e}")
            continue
        
        # 查找"合计"所在的行及其列索引
        try:
            # 先找到含有"合计"的单元格
            total_cell = df.stack().str.contains(useName, na=False)
            # 获取"合计"所在的行索引和列名
            total_positions = total_cell[total_cell].index.tolist()
        except Exception as e:
            print(f"在sheet '{sheet_name}' 中寻找{useName}时发生错误: {e}")
            continue
        
        for position in total_positions:
            row_idx, col_name = position
            # 获取"合计"所在的列数（基于0开始的索引）
            col_number = df.columns.get_loc(col_name)
            value = df.iloc[row_idx, col_number + 4]
            # print(sheet_name, value)
            if not pd.isnull(value):
                name_list.append(sheet_name)
                money_list.append(value)
            
    return name_list, money_list

if __name__ == "__main__":
    filename = "./019—合肥经济技术开发区孙守国中医诊所处方价款(20240601).xls"
    name_list, money_list = get_name_money_list(filename, "合计")
    for x in range(len(money_list)):
        print(name_list[x], money_list[x])


    standard_dates = [extract_and_standardize_date(date) for date in name_list]
    amounts = money_list

    # 构建一个字典来累计每天的金额
    date_amount_sum = {}

    # 假设金额列表与名字日期列表一一对应
    for date, amount in zip(standard_dates, amounts):
        if date in date_amount_sum:
            date_amount_sum[date] += amount
        else:
            date_amount_sum[date] = amount

    sum_money = 0

    # 按日期排序输出每一天的金额总和
    for date, total_amount in sorted(date_amount_sum.items()):
        total_amount = round(total_amount, 2)
        sum_money += total_amount
        print(f"日期 {date} 的总金额为: {total_amount}")

    # 输出总金额合计
    print(f"\n总金额合计为: {sum_money}")